package com.bst.etl.service.impl;

import com.alibaba.druid.pool.DruidDataSource;
import com.bst.common.constant.JobConstant;
import com.bst.common.core.domain.AjaxResult;
import com.bst.common.core.text.Convert;
import com.bst.common.exception.base.BaseException;
import com.bst.common.utils.self.LoginAPIUtils;
import com.bst.common.vo.DataExecParamVO;
import com.bst.etl.domain.EtlDrawBase;
import com.bst.etl.domain.EtlDrawDw;
import com.bst.etl.domain.EtlDrawDwFld;
import com.bst.etl.service.IEtlDrawBaseService;
import com.bst.etl.service.IEtlDrawDwFldService;
import com.bst.etl.service.IEtlDrawDwService;
import com.bst.etl.vo.EtlTaskDwFldVO;
import com.bst.system.framework.datasource.DynamicDataSource;
import com.bst.system.framework.utils.DBUtil;
import com.bst.md.domain.MdMedPubfld;
import com.bst.md.domain.MdMedTb;
import com.bst.md.domain.MdMedTbFld;
import com.bst.etl.mapper.EtlDrawDwFldMapper;
import com.bst.etl.mapper.EtlDrawDwMapper;
import com.bst.system.service.ICommonTableDDLService;
import com.bst.md.service.IMdMedPubfldService;
import com.bst.md.service.IMdMedTbFldService;
import com.bst.md.service.IMdMedTbService;
import com.bst.system.vo.AlterTableParamVO;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.tuple.Triple;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.*;

/**
 * 数据仓库Service业务层处理
 * 
 * @author ruoyi
 * @date 2022-06-20
 */
@Service("etlDrawDwService")
public class EtlDrawDwServiceImpl implements IEtlDrawDwService 
{
    @Autowired
    private EtlDrawDwMapper etlDrawDwMapper;
    @Autowired
    IEtlDrawBaseService drawBaseService;
    @Autowired
    IEtlDrawDwFldService drawDwFldService;
    @Autowired
    IMdMedPubfldService pubfldService;
    @Autowired
    ICommonTableDDLService commonTableDDLService;

    @Autowired
    IMdMedTbService mdMedTbService;
    @Autowired
    IMdMedTbFldService mdMedTbFldService;

    /**
     * 查询数据仓库
     * 
     * @param idDrawDw 数据仓库主键
     * @return 数据仓库
     */
    @Override
    public EtlDrawDw selectEtlDrawDwByIdDrawDw(Long idDrawDw)
    {
        return etlDrawDwMapper.selectEtlDrawDwByIdDrawDw(idDrawDw);
    }

    /**
     * 查询数据仓库列表
     * 
     * @param etlDrawDw 数据仓库
     * @return 数据仓库
     */
    @Override
    public List<EtlDrawDw> selectEtlDrawDwList(EtlDrawDw etlDrawDw)
    {
        return etlDrawDwMapper.selectEtlDrawDwList(etlDrawDw);
    }

    /**
     * 新增数据仓库
     * 
     * @param etlDrawDw 数据仓库
     * @return 结果
     */
    @Override
    public int insertEtlDrawDw(EtlDrawDw etlDrawDw)
    {
        if(etlDrawDw!=null) {
            etlDrawDw.setEuStatus(0);
            etlDrawDw.setDtSysCre(new Date());
            etlDrawDw.setNaEmpCre(LoginAPIUtils.getLoginUsename());
            etlDrawDw.setDtSysModi(new Date());
            etlDrawDw.setNaEmpModi(LoginAPIUtils.getLoginUsename());
        }
        return etlDrawDwMapper.insertEtlDrawDw(etlDrawDw);
    }

    /**
     * 修改数据仓库
     * 
     * @param etlDrawDw 数据仓库
     * @return 结果
     */
    @Override
    public int updateEtlDrawDw(EtlDrawDw etlDrawDw)
    {
        if(etlDrawDw!=null) {
            etlDrawDw.setDtSysModi(new Date());
            etlDrawDw.setNaEmpModi(LoginAPIUtils.getLoginUsename());
        }
        return etlDrawDwMapper.updateEtlDrawDw(etlDrawDw);
    }

    /**
     * 批量删除数据仓库
     * 
     * @param idDrawDws 需要删除的数据仓库主键
     * @return 结果
     */
    @Override
    public int deleteEtlDrawDwByIdDrawDws(String idDrawDws)
    {
        String[] ids = Convert.toStrArray(idDrawDws);
        Long idDrawDw;
        for(String id:ids) {
            idDrawDw = Long.parseLong(id);
            deleteEtlDrawDwByIdDrawDw(idDrawDw);
        }
        return 1;
    }

    @Autowired
    EtlDrawDwFldMapper etlDrawDwFldMapper;
    /**
     * 删除数据仓库信息
     * 
     * @param idDrawDw 数据仓库主键
     * @return 结果
     */
    @Override
    public int deleteEtlDrawDwByIdDrawDw(Long idDrawDw)
    {
        EtlDrawDw drawDw = this.selectEtlDrawDwByIdDrawDw(idDrawDw);

        EtlDrawDwFld dwFld = new EtlDrawDwFld();
        dwFld.setIdDrawDw(idDrawDw);
        etlDrawDwFldMapper.deleteEtlDrawDwFld(dwFld);
        etlDrawDwMapper.deleteEtlDrawDwByIdDrawDw(idDrawDw);
        commonTableDDLService.dropTableByMed(drawDw.getCdTb(),null);
        return 1;
    }



    @Override
    public List<EtlTaskDwFldVO> findAllFld(Long idDrawDw,Long idDrawBase) {
        EtlDrawBase etlDrawBase = drawBaseService.selectEtlDrawBaseByIdDrawBase(idDrawBase);
        Triple<List<String>, List<Integer>, List<String>> columnMetaDataTo =
                DBUtil.getColumnMetaDataTo(DynamicDataSource.getDataSourceById(JobConstant.DB_CLS_DW), etlDrawBase.getCdTbTar());
        //DW字段
        EtlDrawDwFld dwFld = new EtlDrawDwFld();
        dwFld.setIdDrawDw(idDrawDw);
        List<EtlDrawDwFld> etlDrawDwFldList = drawDwFldService.selectEtlDrawDwFldList(dwFld);
        if (etlDrawDwFldList == null) etlDrawDwFldList = new ArrayList<>();
        Map<String,EtlDrawDwFld> dwFldMap = new HashMap<>();
        for (EtlDrawDwFld fld : etlDrawDwFldList) {
            dwFldMap.put(fld.getFldOds().toUpperCase(),fld);
        }
        List<EtlTaskDwFldVO> mappers = new ArrayList<>();
        List<String> left = columnMetaDataTo.getLeft();
        for (String s11 : left) {
            String columnName = s11.toUpperCase();
            boolean isNew = true;
            dwFld = dwFldMap.get(columnName);
            if(dwFld!=null) {
                EtlTaskDwFldVO etlTaskDWFilVO = new EtlTaskDwFldVO();
                etlTaskDWFilVO.setOdsFid(columnName);
                etlTaskDWFilVO.setDwFid1(dwFld.getFldDwMain());
                etlTaskDWFilVO.setDwFid2(dwFld.getFldDwSlave());
                etlTaskDWFilVO.setFgIndex(dwFld.getFgIndex());
                etlTaskDWFilVO.setOdsFidType(1);
                mappers.add(etlTaskDWFilVO);
                dwFldMap.remove(columnName);
            } else {
                EtlTaskDwFldVO etlTaskDWFilVO = new EtlTaskDwFldVO();
                etlTaskDWFilVO.setOdsFid(columnName);
                etlTaskDWFilVO.setOdsFidType(0);
                mappers.add(etlTaskDWFilVO);
            }
        }
        for(EtlDrawDwFld fld:dwFldMap.values()) {
            EtlTaskDwFldVO etlTaskDWFilVO = new EtlTaskDwFldVO();
            etlTaskDWFilVO.setOdsFid(fld.getFldOds());
            etlTaskDWFilVO.setDwFid1(fld.getFldDwMain());
            etlTaskDWFilVO.setDwFid2(fld.getFldDwSlave());
            etlTaskDWFilVO.setFgIndex(fld.getFgIndex());
            etlTaskDWFilVO.setOdsFidType(2);
            mappers.add(etlTaskDWFilVO);
        }
        return mappers;
    }
    @Override
    public AjaxResult createTable(Long idDrawDw){
        EtlDrawDw etlTaskDw = this.selectEtlDrawDwByIdDrawDw(idDrawDw);
        EtlDrawDwFld dwFld = new EtlDrawDwFld();
        dwFld.setIdDrawDw(idDrawDw);
        List<EtlDrawDwFld> dwFldList = drawDwFldService.selectEtlDrawDwFldList(dwFld);
        if (dwFldList.size()==0)
            return AjaxResult.error("未找到DW映射配置");
        Map<String,String> dims = new HashMap<>();
        int indexNum=0;
        for (EtlDrawDwFld fild : dwFldList) {
            if (!StringUtils.isEmpty(fild.getFldDwMain())) {
                dims.put(fild.getFldDwMain(),fild.getFgIndex()==1?JobConstant.SD_CONSTP_INDEX:null);
                if(fild.getFgIndex()==1) {
                    indexNum++;
                }
            }
            if (!StringUtils.isEmpty(fild.getFldDwSlave())) {
                dims.put(fild.getFldDwSlave(),null);
            }
        }
        if(indexNum>5) {
            return AjaxResult.error("最多只能设置五个字段为索引");
        }
        /*if(!dims.keySet().contains(JobConstant.ID_DIM_ORG)) {
            return AjaxResult.error(String.format("[%s]表缺少机构字段", etlTaskDw.getNa()));
        }*/
        MdMedTb mdMedTb = new MdMedTb();
        mdMedTb.setCd(etlTaskDw.getCdTb());
        mdMedTb.setNa(etlTaskDw.getNa());
        mdMedTb.setSdTbca(JobConstant.SD_TBCA_DW);
        mdMedTb.setSdSys(etlTaskDw.getSdSys());
        List<MdMedTbFld> mdMedTbFlds = new ArrayList();
        MdMedTbFld one;
        for (Map.Entry<String,String> dim : dims.entrySet()) {
            one = new MdMedTbFld();
            one.setIdPubfld(dim.getKey());
            one.setSdConstp(dim.getValue());
            mdMedTbFlds.add(one);
        }
        MdMedTb mdMedTbOld = mdMedTbService.selectMdMedTbByCd(etlTaskDw.getCdTb());
        String oldCdTb = null;
        if (mdMedTbOld != null)
            oldCdTb = mdMedTbOld.getCd();
        AjaxResult result = addOtherField(mdMedTbFlds);
        Boolean isSuccess = false;
        if(AjaxResult.isSuccess(result)) {
            result = commonTableDDLService.alterTableByMed(new AlterTableParamVO(mdMedTb, mdMedTbFlds, oldCdTb));
        }
        return result;
    }

    private AjaxResult addOtherField(List<MdMedTbFld> mdMedTbFldList) {
        MdMedTbFld tbFld = new MdMedTbFld();
        tbFld.setIdPubfld(JobConstant.ID);
        tbFld.setSdConstp("pk");
        mdMedTbFldList.add(tbFld);
        List<String> naIdPubFlds = new ArrayList<>();
        List<String> idPubFlds = new ArrayList<>();
        String idPubfld;
        for(MdMedTbFld fld:mdMedTbFldList) {
            idPubfld = fld.getIdPubfld();
            if(idPubfld.startsWith(JobConstant.ID_DIM_PREX)) {
                naIdPubFlds.add(idPubfld.replace(JobConstant.ID_DIM_PREX,JobConstant.NA_DIM_PREX));
            }
            if(idPubfld.startsWith(JobConstant.NA_DIM_PREX)) {
                idPubFlds.add(idPubfld);
            }
        }
        List<String> newNaidPUbFlds = new ArrayList<>();
        for(String fld:naIdPubFlds) {
            if(idPubFlds.contains(fld)) {
                continue;
            }
            newNaidPUbFlds.add(fld);
        }
        if(newNaidPUbFlds.size()>0) {
            List<MdMedPubfld> pubfldList = pubfldService.findByIdList(newNaidPUbFlds);
            if(pubfldList.size()>0) {
                String flds = "";
                for(MdMedPubfld fld:pubfldList) {
                    flds += fld.getIdPubfld();
                }
                return AjaxResult.error("名称字段"+flds+"不存在，请完善sql");
            }
        }
        return AjaxResult.success(null,null);
    }

    @Override
    public List<DataExecParamVO> findParamVO(List<String> ids, Date startDate, Date endDate) {
        EtlDrawDw model = new EtlDrawDw();
        model.setFgAct(1);
        List<EtlDrawDw> dwList = new ArrayList<>();
        for(String id:ids) {
            if(StringUtils.isNotBlank(id)) {
                dwList.add(this.selectEtlDrawDwByIdDrawDw(Long.parseLong(id)));
            }
        }
        return assembleParamVOS(dwList,startDate,endDate);
    }

    @Override
    public List<DataExecParamVO> findParamVOByJobId(Long jobId, Date startDate, Date endDate) {
        List<EtlDrawDw> dwList = etlDrawDwMapper.findByJobId(jobId);
        return assembleParamVOS(dwList,startDate,endDate);
    }

    @Override
    public List<EtlDrawDw> selectEtlDrawDwByDwclses(List<String> sdDwclses) {
        List<EtlDrawDw> dwList = etlDrawDwMapper.selectEtlDrawDwByDwclses(sdDwclses);
        return dwList;
    }

    private List<DataExecParamVO> assembleParamVOS(List<EtlDrawDw> dwList, Date startDate, Date endDate) {
        List<DataExecParamVO> paramList = new ArrayList();
        try {
            DataExecParamVO param;
            for(EtlDrawDw eltTaskDw:dwList) {
                if(StringUtils.isEmpty(eltTaskDw.getCdTb())) {
                    continue;
                }
                String odsToDwQuerySql = this.getOdsToDwQuerySql(eltTaskDw, null);
                String odsToDwBeforeSql = this.getOdsToDwBeforeSql(eltTaskDw, null);
                String taskDes = eltTaskDw.getNa() + "[" + eltTaskDw.getCdTb() + "]";
                param = new DataExecParamVO();
                param.setDelSql(odsToDwBeforeSql);
                param.setStartDate(startDate);
                param.setEndDate(endDate);
                param.setTbTar(eltTaskDw.getCdTb());
                param.setQuerySql(odsToDwQuerySql);
                param.setDes(taskDes);
                param.setJobType(JobConstant.JOB_TYPE_DW);
                param.setIdDbSou(JobConstant.DB_CLS_DW);
                param.setIdDbTar(JobConstant.DB_CLS_DW);
                paramList.add(param);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return paramList;
    }

    private String getOdsToDwQuerySql(EtlDrawDw eltTaskDw, String where) throws BaseException {
        EtlDrawDwFld mdMedOdsDwFidModel = new EtlDrawDwFld();
        mdMedOdsDwFidModel.setIdDrawDw(eltTaskDw.getIdDrawDw());
        List<EtlDrawDwFld> byModel = drawDwFldService.selectEtlDrawDwFldList(mdMedOdsDwFidModel);
        if (byModel == null || byModel.size() == 0)
            throw new BaseException("DW"+eltTaskDw.getCdTb()+"映射关系配置记录未找到");

        DruidDataSource dataSource = DynamicDataSource.getDataSourceById(JobConstant.DB_CLS_DW);
        if (dataSource == null) new BaseException("获取DW数据库失败");
        List<String> columns = new ArrayList<>();
        for (EtlDrawDwFld mdMedOdsDwFid : byModel) {
            String odsFid = mdMedOdsDwFid.getFldOds();
            String idPubfid1 = mdMedOdsDwFid.getFldDwMain();
            String idPubfid2 = mdMedOdsDwFid.getFldDwSlave();

            if (!StringUtils.isEmpty(idPubfid1)) {
                columns.add(caseColumnHandle(odsFid, idPubfid1.toUpperCase(), dataSource.getDbType()) + " " + idPubfid1.toUpperCase());
            }
            if (!StringUtils.isEmpty(idPubfid2)) {
                columns.add(caseColumnHandle(odsFid, idPubfid2.toUpperCase(), dataSource.getDbType()) + " " + idPubfid2.toUpperCase());
            }
        }
        StringBuilder sb = new StringBuilder();
        sb.append("select ").append(StringUtils.join(columns, " , "))
                .append(" from ").append(eltTaskDw.getCdTbTarOds());
        if (!StringUtils.isEmpty(eltTaskDw.getFldDt())) {
            sb.append(" where ").append(eltTaskDw.getFldDt()).append(" >= ${DT_DAY_BEGIN} and ")
                    .append(eltTaskDw.getFldDt()).append(" < ${DT_DAY_END} ");
        }
        if (!StringUtils.isEmpty(where)) {
            if (!StringUtils.isEmpty(eltTaskDw.getFldDt())) {
                sb.append(" and ");
            } else {
                sb.append(" where ");
            }
            sb.append(where);
        }
        return sb.toString();

    }

    /**
     * 包含 DIM_DAY 与 DIM_TIME 的维度 时间转INT
     *
     * @param odsFid
     * @param idPubfid
     * @param euTp
     * @return
     * @throws BaseException
     */
    private String caseColumnHandle(String odsFid, String idPubfid, String euTp) throws BaseException {
        if (idPubfid.toUpperCase().startsWith("ID_DIM_DAY")) {
            if (JobConstant.DB_TYPE_MYSQL.equals(euTp)) {
                return "CAST(DATE_FORMAT(" + odsFid + ",'%Y%m%d')AS SIGNED)";
            } else if (JobConstant.DB_TYPE_SQLSERVER.equals(euTp)) {
                return "cast(CONVERT(varchar(100), " + odsFid + ", 112) as int)";
            } else if (JobConstant.DB_TYPE_ORACLE.equals(euTp)) {
                return "to_char(" + odsFid + ",'yyyymmdd')";
            }  else if (JobConstant.DB_TYPE_POSTGREY.equals(euTp)) {
                return "to_char(" + odsFid + ",'yyyymmdd')";
            } else {
                throw new BaseException(String.format("数据库类型错误[%s]", euTp));
            }

        } else if (idPubfid.toUpperCase().startsWith("ID_DIM_TIME")) {
            if (JobConstant.DB_TYPE_MYSQL.equals(euTp)) {
                return "DATE_FORMAT(" + odsFid + ",'%H%i')";
            } else if (JobConstant.DB_TYPE_SQLSERVER.equals(euTp)) {
                return "replace(substring(CONVERT(varchar(100), " + odsFid + ", 108),1,5),':','')";
            } else if (JobConstant.DB_TYPE_ORACLE.equals(euTp)) {
                return "to_char(" + odsFid + ",'HH24MI')";
            }  else if (JobConstant.DB_TYPE_POSTGREY.equals(euTp)) {
                return "to_char(" + odsFid + ",'HH24MI')";
            } else {
                throw new BaseException(String.format("数据库类型错误[%s]", euTp));
            }
        } else {
            return odsFid;
        }
    }

    private String getOdsToDwBeforeSql(EtlDrawDw eltTaskDw, String where) throws BaseException {
        EtlDrawDwFld mdMedOdsDwFidModel = new EtlDrawDwFld();
        mdMedOdsDwFidModel.setIdDrawDw(eltTaskDw.getIdDrawDw());
        StringBuilder sb = new StringBuilder();
        if (!StringUtils.isEmpty(eltTaskDw.getFldDt())) {
            String colimnName = null;
            List<EtlDrawDwFld> byModel = drawDwFldService.selectEtlDrawDwFldList(mdMedOdsDwFidModel);
            for (EtlDrawDwFld mdMedOdsDwFid : byModel) {
                if (eltTaskDw.getFldDt().toUpperCase().equals(mdMedOdsDwFid.getFldOds().toUpperCase())) {
                    colimnName = mdMedOdsDwFid.getFldDwMain().toUpperCase();
                    break;
                }
            }
            sb.append("delete from ").append(eltTaskDw.getCdTb())
                    .append(" where ").append(colimnName).append(" >= '${DT_DAY_BEGIN_INT}' and ")
                    .append(colimnName).append(" < '${DT_DAY_END_INT}' ");
            if (!StringUtils.isEmpty(where)) {
                sb.append(" and ").append(where);
            }
        } else {
            sb.append("truncate table ").append(eltTaskDw.getCdTb()).append(" ;");
        }
        return sb.toString();

    }
}
